package com.sapient;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class MarketCreate
 */
@WebServlet("/MarketCreate")
public class MarketCreate extends HttpServlet {
	String[] result = null;
	String[] result2 = null;
	String[] result3= null;
	String[] result4 = null;

	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public MarketCreate() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int rows = 0;
		String selected = request.getParameter("selected");
		ResultSet rs = null;
		String[] marketdetails = null; ;
		Connection conn = null;   
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			String SQL = "";

			SQL = "select MarketName from Market ";
			rs= st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			rs= st.executeQuery(SQL);
			result = new String[rows];
			int count = 0;
			while(rs.next())
			{
				result[count] = rs.getString(1);
				count++; 
			}

			//****LOCATION DATA***//*
			rows = 0;
			SQL = "select locationName from Location ";
			rs= st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			result2 = new String[rows];
			rs=st.executeQuery(SQL);
			count = 0;
			while(rs.next())
			{
				result2[count] = rs.getString(1);
				count++; 
			}

			//****COMMODITY DATA***//*
			rows = 0;

			SQL = "select CommodityType from Commodity ";
			rs= st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			result3 = new String[rows];
			rs=st.executeQuery(SQL);
			count = 0;
			while(rs.next())
			{
				result3[count] = rs.getString(1);
				count++; 
			}

			//****CURRENCY DATA***//*
			rows = 0;

			SQL = "select currencyName from Currency ";
			rs= st.executeQuery(SQL);
			while(rs.next()){
				rows++;
			}
			result4 = new String[rows];
			rs=st.executeQuery(SQL);
			count = 0;
			while(rs.next())
			{
				result4[count] = rs.getString(1);
				count++; 
			}

			conn.close(); 

		} catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 
		request.setAttribute("market", result);
		request.setAttribute("Location", result2);
		request.setAttribute("Commodity", result3);
		request.setAttribute("Currency", result4);

		RequestDispatcher m = getServletContext().getRequestDispatcher("/Market.jsp");
		m.forward(request, response);
	} 

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String MarketName = request.getParameter("MarketName");
		String Location = request.getParameter("selected1");
		String CommodityType = request.getParameter("selected2");
		String Currency = request.getParameter("selected3");
		String startDate=request.getParameter("startDate");
		String endDate=request.getParameter("endDate");
		if(endDate != "")
			endDate = endDate.substring(0, 10);
		if(startDate != "")
			startDate = startDate.substring(0, 10);
		String status = "";   
		Connection conn = null;                
		try
		{ 
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			Statement st = conn.createStatement(); 
			
			
			String SQL1 = "select * from market where location = '"+Location+"' and commodityType = '"
					+ ""+CommodityType+"' and currency = '"+Currency+"' and marketname = '"+MarketName+"'"; 
			
			System.out.println(SQL1);
			ResultSet rs = st.executeQuery(SQL1);
			if(rs.next()){
				status = "Entity creation failed. Market details need to be unique.";
			}
			else{
				String SQL = "";
				 SQL = "INSERT INTO Market (marketid,marketname,Location,CommodityType,Currency,StartDate,enddate, flag,marketflag) VALUES (marketseq.nextval,'"+MarketName+"', '"+Location+"',"
						+ "'"+ CommodityType+"','"+Currency+ "',to_date('" +startDate+"','RR/MM/DD'),"+
        		"to_date('" +endDate+"','RR/MM/DD'),'N','N')";

				System.out.println(SQL);
				st.executeUpdate(SQL);
				status = "Entity created!";
			}

			conn.close();
		} catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		}
		request.setAttribute("status", status);
		doGet(request,response);
	}

}
